"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 人为因素
Case Name   : 普通用户删除修改pg_class
Create At   : 2020/10/14
@zou_jialiang0505328126
Description :
    1. 创建普通用户
    2. 使用该用户创建表
    3. 使用该用户修改系统表relname
    4. 使用该用户删除系统表
Expect      :
    1.成功
    2.成功
    3.提示无权限
    4.提示无权限
History     :
    2021/8/31 @wan005: 修改格式
"""

import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.CommonSH import RestartDbCluster

logger = Logger()
common = Common()
commonshpri = CommonSH('PrimaryDbUser')


class ModifyConnection(unittest.TestCase):
    dbPrimaryUserNode = Node(node='PrimaryDbUser')
    Constant = Constant()
    table_name = "test_table_021"
    table_name_bak = "test_table_021_bak"
    user = 'opengauss_reliability_human_case021_user'
    user_password = 'mima@123'

    nodes_tuple = ('PrimaryDbUser', 'Standby1DbUser', 'Standby2DbUser')

    @RestartDbCluster(*nodes_tuple)
    def setUp(self):
        logger.info("-----------this is setup-----------")
        logger.info("--------Opengauss_Reliability_Human_Case021 start----")

    def test_modify_pidfile(self):
        text = '----step1:创建普通用户 expect:成功----'
        logger.info(text)
        result = commonshpri.executDbSql(
            f"create user {self.user} with password '{self.user_password}';")
        logger.info(result)
        self.assertIn("CREATE ROLE", result, '执行失败:' + text)

        text = '----step2:使用该用户创建表 expect:成功----'
        logger.info(text)
        sql = f'create table {self.table_name}(i int);'
        shell = f'source {macro.DB_ENV_PATH};' \
            f'gsql -d {self.dbPrimaryUserNode.db_name} ' \
            f'-p {self.dbPrimaryUserNode.db_port} ' \
            f'-U "{self.user}" -W "{self.user_password}" -c "{sql}";'
        logger.info(shell)
        msg = self.dbPrimaryUserNode.sh(shell).result()
        logger.info('executDbSql: ' + sql)
        logger.info(msg)
        self.assertIn(self.Constant.TABLE_CREATE_SUCCESS, msg, '执行失败:' + text)

        text = '----step3:使用该用户修改系统表relname expect:提示无权限----'
        logger.info(text)
        sql = f"update pg_class set relname='{self.table_name_bak}' " \
            f"where relname='{self.table_name}'"
        shell = f'source {macro.DB_ENV_PATH};' \
            f'gsql -d {self.dbPrimaryUserNode.db_name} ' \
            f'-p {self.dbPrimaryUserNode.db_port} ' \
            f'-U "{self.user}" -W "{self.user_password}" -c "{sql}";'
        logger.info(shell)
        msg = self.dbPrimaryUserNode.sh(shell).result()
        logger.info('executDbSql: ' + sql)
        logger.info(msg)
        self.assertIn(self.Constant.LOW_PERM_DENIED_MSG, msg, '执行失败:' + text)

        text = '----step4:使用该用户删除系统表 expect:提示无权限----'
        logger.info(text)
        sql = f"delete from pg_class where relname='{self.table_name}';"
        shell = f'source {macro.DB_ENV_PATH};' \
            f'gsql -d {self.dbPrimaryUserNode.db_name} ' \
            f'-p {self.dbPrimaryUserNode.db_port} ' \
            f'-U "{self.user}" -W "{self.user_password}" -c "{sql}";'
        logger.info(shell)
        msg = self.dbPrimaryUserNode.sh(shell).result()
        logger.info('executDbSql: ' + sql)
        logger.info(msg)
        self.assertIn(self.Constant.LOW_PERM_DENIED_MSG, msg, '执行失败:' + text)

    def tearDown(self):
        logger.info('----------------this is tearDown-----------------------')
        logger.info("-----------drop database--------------------")
        sql = f'drop table if exists {self.table_name};'
        shell = f'source {macro.DB_ENV_PATH};' \
            f'gsql -d {self.dbPrimaryUserNode.db_name} ' \
            f'-p {self.dbPrimaryUserNode.db_port} ' \
            f'-U "{self.user}" -W "{self.user_password}" -c "{sql}";'
        logger.info(shell)
        msg = self.dbPrimaryUserNode.sh(shell).result()
        logger.info(msg)

        logger.info("-----------drop user--------------------")
        sql = f'drop user if exists {self.user};'
        msg = commonshpri.executDbSql(sql)
        logger.info(msg)
